﻿namespace Hidistro.SqlDal.Members
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Entities;
    using Hidistro.Entities.Members;
    using Hidistro.Membership.Context;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Runtime.InteropServices;
    using System.Text;

    public class MemberDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public bool ChangeMemberGrade(int userId, int gradId, int points)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT ISNULL(Points, 0) AS Point, GradeId FROM aspnet_MemberGrades Order by Point Desc ");
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    if (((int) reader["GradeId"]) == gradId)
                    {
                        break;
                    }
                    if (((int) reader["Point"]) <= points)
                    {
                        return this.UpdateUserRank(userId, (int) reader["GradeId"]);
                    }
                }
                return true;
            }
        }

        public bool Delete(int userId)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Member_Delete");
            Member user = Users.GetUser(userId) as Member;
            this.database.AddInParameter(storedProcCommand, "UserId", DbType.Int32, userId);
            this.database.AddInParameter(storedProcCommand, "UserName", DbType.String, user.Username);
            this.database.AddParameter(storedProcCommand, "ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, string.Empty, DataRowVersion.Default, null);
            this.database.ExecuteNonQuery(storedProcCommand);
            object parameterValue = this.database.GetParameterValue(storedProcCommand, "ReturnValue");
            return (((parameterValue != null) && (parameterValue != DBNull.Value)) && (Convert.ToInt32(parameterValue) == 0));
        }

        public Dictionary<int, MemberClientSet> GetMemberClientSet()
        {
            Dictionary<int, MemberClientSet> dictionary = new Dictionary<int, MemberClientSet>();
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_MemberClientSet");
            MemberClientSet set = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    set = DataMapper.PopulateMemberClientSet(reader);
                    dictionary.Add(set.ClientTypeId, set);
                }
            }
            return dictionary;
        }

        public DbQueryResult GetMembers(MemberQuery query)
        {
            object obj2;
            StringBuilder builder = new StringBuilder();
            if (query.HasVipCard.HasValue)
            {
                if (query.HasVipCard.Value)
                {
                    builder.Append("VipCardNumber is not null");
                }
                else
                {
                    builder.Append("VipCardNumber is null");
                }
            }
            if (query.GradeId.HasValue)
            {
                if (builder.Length > 0)
                {
                    builder.Append(" AND ");
                }
                builder.AppendFormat("GradeId = {0}", query.GradeId.Value);
            }
            if (query.IsApproved.HasValue)
            {
                if (builder.Length > 0)
                {
                    builder.Append(" AND ");
                }
                builder.AppendFormat("m.userid in(select u1.userid from aspnet_users as u1 where  IsApproved = '{0}')", query.IsApproved.Value);
            }
            if (!string.IsNullOrEmpty(query.Username))
            {
                if (builder.Length > 0)
                {
                    builder.Append(" AND ");
                }
                builder.AppendFormat("m.userid in(select u1.userid from aspnet_users as u1 where username LIKE '%{0}%')", DataHelper.CleanSearchString(query.Username));
            }
            if (!string.IsNullOrEmpty(query.Realname))
            {
                if (builder.Length > 0)
                {
                    builder.AppendFormat(" AND ", new object[0]);
                }
                builder.AppendFormat("RealName LIKE '%{0}%'", DataHelper.CleanSearchString(query.Realname));
            }
            string str = "";
            if (!string.IsNullOrEmpty(query.ClientType))
            {
                string clientType = query.ClientType;
                if (clientType == null)
                {
                    goto Label_0533;
                }
                if (!(clientType == "new"))
                {
                    if (clientType == "activy")
                    {
                        str = "SELECT UserId FROM Hishop_Orders WHERE 1=1";
                        if (query.OrderNumber.HasValue)
                        {
                            obj2 = str;
                            obj2 = string.Concat(new object[] { obj2, " AND datediff(dd,OrderDate,'", query.StartTime.Value.Date, "')<=0" });
                            obj2 = string.Concat(new object[] { obj2, " AND datediff(dd,OrderDate,'", query.EndTime.Value.Date, "')>=0" });
                            str = string.Concat(new object[] { obj2, " GROUP BY UserId HAVING COUNT(*)", query.CharSymbol, query.OrderNumber.Value });
                        }
                        if (query.OrderMoney.HasValue)
                        {
                            obj2 = str;
                            obj2 = string.Concat(new object[] { obj2, " AND datediff(dd,OrderDate,'", query.StartTime.Value.Date, "')<=0" });
                            obj2 = string.Concat(new object[] { obj2, " AND datediff(dd,OrderDate,'", query.EndTime.Value.Date, "')>=0" });
                            str = string.Concat(new object[] { obj2, " GROUP BY UserId HAVING SUM(OrderTotal)", query.CharSymbol, query.OrderMoney.Value });
                        }
                        if (builder.Length > 0)
                        {
                            builder.AppendFormat(" AND ", new object[0]);
                        }
                        builder.AppendFormat("UserId IN (" + str + ")", new object[0]);
                        goto Label_0621;
                    }
                    goto Label_0533;
                }
                str = "SElECT UserId FROM aspnet_Members WHERE 1=1";
                if (query.StartTime.HasValue)
                {
                    obj2 = str;
                    str = string.Concat(new object[] { obj2, " AND datediff(dd,VipCardDate,'", query.StartTime.Value.Date, "')<=0" });
                }
                if (query.EndTime.HasValue)
                {
                    obj2 = str;
                    str = string.Concat(new object[] { obj2, " AND datediff(dd,VipCardDate,'", query.EndTime.Value.Date, "')>=0" });
                }
                if (builder.Length > 0)
                {
                    builder.AppendFormat(" AND ", new object[0]);
                }
                builder.Append("UserId IN (" + str + ")");
            }
            goto Label_0621;
        Label_0533:
            str = "SELECT UserId FROM Hishop_Orders WHERE 1=1";
            obj2 = str;
            obj2 = string.Concat(new object[] { obj2, " AND datediff(dd,OrderDate,'", query.StartTime.Value.Date, "')<=0" });
            str = string.Concat(new object[] { obj2, " AND datediff(dd,OrderDate,'", query.EndTime.Value.Date, "')>=0" }) + " GROUP BY UserId";
            if (builder.Length > 0)
            {
                builder.AppendFormat(" AND ", new object[0]);
            }
            builder.AppendFormat("UserId NOT IN (" + str + ")", new object[0]);
        Label_0621:
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "aspnet_Members m", "UserId", (builder.Length > 0) ? builder.ToString() : null, "*, (SELECT Name FROM aspnet_MemberGrades WHERE GradeId = m.GradeId) AS GradeName,(select u.username from aspnet_users as u where u.userid=m.userid)  as username,(select u.email from aspnet_users as u where u.userid=m.userid)  as email");
        }

        public DataTable GetMembersNopage(MemberQuery query, IList<string> fields)
        {
            if (fields.Count == 0)
            {
                return null;
            }
            DataTable table = null;
            string str = string.Empty;
            foreach (string str2 in fields)
            {
                str = str + str2 + ",";
            }
            str = str.Substring(0, str.Length - 1);
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SELECT {0} FROM aspnet_Members as m,aspnet_users as u WHERE m.UserID=u.UserID ", str);
            if (!string.IsNullOrEmpty(query.Username))
            {
                builder.AppendFormat(" AND UserName LIKE '%{0}%'", query.Username);
            }
            if (query.GradeId.HasValue)
            {
                builder.AppendFormat(" AND GradeId={0}", query.GradeId);
            }
            if (query.HasVipCard.HasValue)
            {
                if (query.HasVipCard.Value)
                {
                    builder.Append(" AND VipCardNumber is not null");
                }
                else
                {
                    builder.Append(" AND VipCardNumber is null");
                }
            }
            if (!string.IsNullOrEmpty(query.Realname))
            {
                builder.AppendFormat(" AND Realname LIKE '%{0}%'", query.Realname);
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                table = DataHelper.ConverDataReaderToDataTable(reader);
                reader.Close();
            }
            return table;
        }

        public DbQueryResult GetMyReferralMembers(MemberQuery query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("ReferralUserId = {0}", HiContext.Current.User.UserId);
            if (query.GradeId.HasValue)
            {
                builder.AppendFormat(" AND GradeId = {0}", query.GradeId.Value);
            }
            if (!string.IsNullOrEmpty(query.Username))
            {
                builder.AppendFormat(" AND UserName LIKE '%{0}%'", DataHelper.CleanSearchString(query.Username));
            }
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_aspnet_Members", "UserId", (builder.Length > 0) ? builder.ToString() : null, "*");
        }

        public void GetStatisticsNum(out int noPayOrderNum, out int noReadMessageNum, out int noReplyLeaveCommentNum)
        {
            int num = 0;
            int num2 = 0;
            int num3 = 0;
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SELECT COUNT(*) AS NoPayOrderNum FROM Hishop_Orders WHERE UserId = {0} AND OrderStatus = {1};", HiContext.Current.User.UserId, 1);
            builder.AppendFormat(" SELECT COUNT(*) AS NoReadMessageNum FROM Hishop_MemberMessageBox WHERE Accepter = '{0}' AND IsRead=0 ;", HiContext.Current.User.Username);
            builder.AppendFormat(" SELECT COUNT(*) AS NoReplyLeaveCommentNum FROM Hishop_ProductConsultations WHERE UserId = {0} AND ViewDate is null AND ReplyUserId is not null;", HiContext.Current.User.UserId);
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read() && (DBNull.Value != reader["NoPayOrderNum"]))
                {
                    num = (int) reader["NoPayOrderNum"];
                }
                if ((reader.NextResult() && reader.Read()) && (DBNull.Value != reader["NoReadMessageNum"]))
                {
                    num2 = (int) reader["NoReadMessageNum"];
                }
                if ((reader.NextResult() && reader.Read()) && (DBNull.Value != reader["NoReplyLeaveCommentNum"]))
                {
                    num3 = (int) reader["NoReplyLeaveCommentNum"];
                }
            }
            noPayOrderNum = num;
            noReadMessageNum = num2;
            noReplyLeaveCommentNum = num3;
        }

        public bool InsertClientSet(Dictionary<int, MemberClientSet> clientsets)
        {
            StringBuilder builder = new StringBuilder("DELETE FROM  [Hishop_MemberClientSet];");
            foreach (KeyValuePair<int, MemberClientSet> pair in clientsets)
            {
                string str = "";
                string str2 = "";
                if (pair.Value.StartTime.HasValue)
                {
                    str = pair.Value.StartTime.Value.ToString("yyyy-MM-dd");
                }
                if (pair.Value.EndTime.HasValue)
                {
                    str2 = pair.Value.EndTime.Value.ToString("yyyy-MM-dd");
                }
                builder.AppendFormat(string.Concat(new object[] { "INSERT INTO Hishop_MemberClientSet(ClientTypeId,StartTime,EndTime,LastDay,ClientChar,ClientValue) VALUES (", pair.Key, ",'", str, "','", str2, "',", pair.Value.LastDay, ",'", pair.Value.ClientChar, "',", pair.Value.ClientValue, ");" }), new object[0]);
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool UpdateMemberAccount(decimal orderTotal, int userId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE aspnet_Members SET Expenditure = ISNULL(Expenditure,0) + @OrderPrice, OrderNumber = ISNULL(OrderNumber,0) + 1 WHERE UserId = @UserId");
            this.database.AddInParameter(sqlStringCommand, "OrderPrice", DbType.Decimal, orderTotal);
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        private bool UpdateUserRank(int userId, int gradeId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE aspnet_Members SET GradeId = @GradeId WHERE UserId = @UserId");
            this.database.AddInParameter(sqlStringCommand, "GradeId", DbType.Int32, gradeId);
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public void UpdateUserStatistics(int userId, decimal refundAmount, bool isAllRefund)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE aspnet_Members SET Expenditure = ISNULL(Expenditure,0) - @refundAmount, OrderNumber = ISNULL(OrderNumber,0) - @refundNum WHERE UserId = @UserId");
            this.database.AddInParameter(sqlStringCommand, "refundAmount", DbType.Decimal, refundAmount);
            this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, userId);
            if (isAllRefund)
            {
                this.database.AddInParameter(sqlStringCommand, "refundNum", DbType.Int32, 1);
            }
            else
            {
                this.database.AddInParameter(sqlStringCommand, "refundNum", DbType.Int32, 0);
            }
            this.database.ExecuteNonQuery(sqlStringCommand);
        }
    }
}

